const express = require('express');
const router = express.Router();
const mysql = require('mysql2/promise');
const dbConfig = require('../config/database');

// 导出图书数据（注意：这个路由必须在 /:id 路由之前）
router.get('/export', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        // 获取所有图书数据，不使用分页
        const [rows] = await connection.execute('SELECT * FROM books ORDER BY created_at DESC');
        await connection.end();

        // 生成 CSV 数据
        const csvHeader = 'ISBN,书名,作者,出版社,库存,创建时间\n';
        const csvData = rows.map(book =>
            `${book.isbn},"${book.title}","${book.author}","${book.publisher}",${book.stock},"${book.created_at}"`
        ).join('\n');

        res.setHeader('Content-Type', 'text/csv; charset=utf-8');
        res.setHeader('Content-Disposition', 'attachment; filename=books.csv');
        res.send(csvHeader + csvData);
    } catch (error) {
        console.error('导出图书数据失败:', error);
        res.status(500).json({ error: '导出数据失败' });
    }
});

// 获取所有图书（带分页和排序）
router.get('/', async (req, res) => {
    try {
        const page = parseInt(req.query.page) || 1;
        const limit = parseInt(req.query.limit) || 8;
        const offset = (page - 1) * limit;
        const sortColumn = req.query.sortColumn || 'created_at';
        const sortDirection = req.query.sortDirection || 'desc';

        const connection = await mysql.createConnection(dbConfig);

        // 获取总记录数
        const [countResult] = await connection.execute('SELECT COUNT(*) as total FROM books');
        const total = countResult[0].total;

        // 构建排序查询
        const [rows] = await connection.execute(
            `SELECT * FROM books ORDER BY ${sortColumn} ${sortDirection} LIMIT ${limit} OFFSET ${offset}`
        );

        await connection.end();

        res.json({
            data: rows,
            pagination: {
                page,
                limit,
                total,
                totalPages: Math.ceil(total / limit)
            }
        });
    } catch (error) {
        console.error('获取图书列表失败:', error);
        res.status(500).json({ error: '获取图书列表失败' });
    }
});

// 搜索图书
router.get('/search', async (req, res) => {
    try {
        const { q } = req.query;
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute(
            'SELECT * FROM books WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ?',
            [`%${q}%`, `%${q}%`, `%${q}%`]
        );
        await connection.end();
        res.json(rows);
    } catch (error) {
        res.status(500).json({ error: '搜索图书失败' });
    }
});

// 获取单本图书
router.get('/:id', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);
        const [rows] = await connection.execute('SELECT * FROM books WHERE id = ?', [req.params.id]);
        await connection.end();

        if (rows.length === 0) {
            return res.status(404).json({ error: '图书不存在' });
        }
        res.json(rows[0]);
    } catch (error) {
        res.status(500).json({ error: '获取图书信息失败' });
    }
});

// 添加图书
router.post('/', async (req, res) => {
    try {
        const { isbn, title, author, publisher, stock } = req.body;

        // 添加输入验证
        if (!isbn || !title || !author) {
            return res.status(400).json({ error: '必填字段不能为空' });
        }

        const connection = await mysql.createConnection(dbConfig);

        // 检查 ISBN 是否已存在
        const [existing] = await connection.execute(
            'SELECT id FROM books WHERE isbn = ?',
            [isbn]
        );

        if (existing.length > 0) {
            await connection.end();
            return res.status(400).json({ error: '该ISBN已存在' });
        }

        // 确保 stock 是数字类型
        const stockNum = parseInt(stock) || 0;

        // 插入新图书
        const [result] = await connection.execute(
            'INSERT INTO books (isbn, title, author, publisher, stock) VALUES (?, ?, ?, ?, ?)',
            [isbn, title, author, publisher || '', stockNum]
        );

        await connection.end();

        // 返回新添加的图书信息
        res.status(201).json({
            id: result.insertId,
            isbn,
            title,
            author,
            publisher: publisher || '',
            stock: stockNum
        });
    } catch (error) {
        console.error('添加图书失败:', error); // 添加错误日志
        res.status(500).json({
            error: '添加图书失败',
            details: error.message  // 返回详细错误信息
        });
    }
});

// 更新图书
router.put('/:id', async (req, res) => {
    try {
        const { isbn, title, author, publisher, stock } = req.body;
        const connection = await mysql.createConnection(dbConfig);

        const [result] = await connection.execute(
            'UPDATE books SET isbn = ?, title = ?, author = ?, publisher = ?, stock = ? WHERE id = ?',
            [isbn, title, author, publisher, stock, req.params.id]
        );

        await connection.end();

        if (result.affectedRows === 0) {
            return res.status(404).json({ error: '图书不存在' });
        }
        res.json({ id: req.params.id, ...req.body });
    } catch (error) {
        res.status(500).json({ error: '更新图书失败' });
    }
});

// 删除图书
router.delete('/:id', async (req, res) => {
    try {
        const connection = await mysql.createConnection(dbConfig);

        // 检查图书是否存在
        const [book] = await connection.execute(
            'SELECT * FROM books WHERE id = ?',
            [req.params.id]
        );

        if (book.length === 0) {
            await connection.end();
            return res.status(404).json({ error: '图书不存在' });
        }

        // 检查是否有未归还的借阅记录
        const [borrowings] = await connection.execute(
            'SELECT id FROM borrowings WHERE book_id = ? AND status = "borrowed"',
            [req.params.id]
        );

        if (borrowings.length > 0) {
            await connection.end();
            return res.status(400).json({ error: '该图书有未归还的借阅记录，无法删除' });
        }

        const [result] = await connection.execute('DELETE FROM books WHERE id = ?', [req.params.id]);
        await connection.end();

        if (result.affectedRows === 0) {
            return res.status(404).json({ error: '图书不存在' });
        }
        res.status(204).end();
    } catch (error) {
        console.error('删除图书失败:', error);
        res.status(500).json({ error: '删除图书失败' });
    }
});

module.exports = router; 